const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);
let connectDAO = require("./connect.js");

// 登陆
router.get("/login", function (req, res) {
  const { PhoneNumber, Password } = req.query;
  const sql = `SELECT * FROM Officers WHERE PhoneNumber = '${PhoneNumber}' AND Password = '${Password}';`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        data: records[0],
      });
    } else {
      res.send({
        code: 201,
        msg: "账号或密码不正确",
      });
    }
  });
});

// 注册
router.post("/register", function (req, res) {
  const { Name, Department, PhoneNumber } = req.body;
  const sql = `INSERT INTO Officers (Name, Gender,Rank,Department,PhoneNumber, Permission,Password,HireDate) VALUES 
  ('${Name}', 'Male', '警员', '${Department || "派出所"}', '${PhoneNumber}', 'user','${PhoneNumber.slice(
    -6
  )}', '${dayjs().format("YYYY-MM-DD")}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});


// 获取所有科普知识
router.get("/get_science_records", function (req, res) {
  const { Category } = req.query;
  const sql = `SELECT * FROM CyberSecurityKnowledge ${Category ? 'WHERE Category = ' + Category : ''}`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        });
      } else {
        res.send({
          code: 200,
          msg: "no data",
        })
      }
    })
});

// 提交科普知识
router.post("/submit_science_records", function (req, res) {
  const { Title, Content, Category, AuthorID, CoverImage } = req.body;
  const sql = `INSERT INTO CyberSecurityKnowledge (Title, Content, Category, AuthorID,CoverImage) VALUES 
  ('${Title}', '${Content}', '${Category}', '${AuthorID}', '${CoverImage}')`
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
})


// 获取所有网络安全案例
router.get("/get_network_security_case", function (req, res) {
  const sql = `SELECT Title,CaseID FROM SecurityCase`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        })
      } else {
        res.send({
          code: 200,
          msg: "no data",
        })
      }
    })
})

// 获取案例详情
router.get("/get_network_security_case_detail", function (req, res) {
  const { CaseID } = req.query;
  const sql = `SELECT * FROM SecurityCase WHERE CaseID = ${CaseID}`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          data: records[0]
        })
      }
    })
})

// 获取案例评论
router.get("/get_network_security_case_comment", function (req, res) {
  const { CaseID } = req.query;
  const sql = `SELECT * FROM Comment WHERE CaseID = ${CaseID}`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          data: records
        })
      } else {
        res.send({
          code: 200,
          msg: "no data",
        })
      }
    })
})

// 提交网络安全案例
router.post("/submit_network_security_case", function (req, res) {
  const { Title, Description, Category, AuthorID, DeceivedReason, PreventiveSuggestions } = req.body;
  const sql = `INSERT INTO SecurityCase (Title, Description, Category, AuthorID,DeceivedReason,PreventiveSuggestions) VALUES 
  ('${Title}', '${Description}', '${Category}', '${AuthorID}','${DeceivedReason}','${PreventiveSuggestions}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
})

//发表案例评论
router.post("/submit_network_security_case_comment", function (req, res) {
  const { CaseID, Content, OfficerID, OfficerName } = req.body;
  const sql = `INSERT INTO Comment (CaseID, Content, OfficerID, OfficerName, CreateTime) VALUES 
  ('${CaseID}', '${Content}', '${OfficerID}', '${OfficerName}','${dayjs().format("YYYY-MM-DD HH:mm:ss")}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
})

// 发布用户建议反馈
router.post("/submit_feedback", function (req, res) {
  const { OfficerID, Type, Content, Contact } = req.body;
  const sql = `INSERT INTO SecurityFeedback (OfficerID, Type, Content,Contact,CreateTime) VALUES
  ('${OfficerID}', '${Type}', '${Content}','${Contact}','${dayjs().format("YYYY-MM-DD HH:mm:ss")}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
})
// 获取反馈建议
router.get("/get_feedback_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT * FROM SecurityFeedback ${OfficerID ? 'WHERE OfficerID = ' + OfficerID : ''} `;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        });
      } else {
        res.send({
          code: 200,
          msg: "nodata",
        })
      }
    })

});

// 回复用户建议反馈
router.post("/submit_feedback_reply", function (req, res) {
  const { FeedbackID, ReplyContent, ReplyID } = req.body;
  const sql = `UPDATE SecurityFeedback SET Status = 'resolved', ReplyContent = '${ReplyContent}', ReplyID = '${ReplyID}' WHERE FeedbackID = ${FeedbackID} `
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })

})


// 获取officers
router.get("/get_officers_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT * FROM Officers ${OfficerID ? "WHERE OfficerID = " + OfficerID : ""
    }`; connectDAO
      .queryDataBySQL(sql, (records) => {
        if (records.length > 0) {
          res.send({
            code: 200,
            msg: "Success",
            records,
            total: records.length,
          });
        } else {
          res.send({
            code: 200,
            msg: "no data",
            records,
          });
        }
      })
      .catch((err) => {
        res.send({ code: 201, msg: "error", err });
      });
});

module.exports = router;
